SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[GetTopXIssuersByMV] 
@FundID  VARCHAR(10)
 ,@NAVDate DATE
 ,@NumberOfResults INT
 
AS
BEGIN
 
 /*DEBUG
 --SET @FundID = 'FSIC'
 --SET @NAVDate = '6/19/2013'
 --SET @NumberOfResults = 15
 */
 
 DECLARE @top5Names TABLE
      (
           IssName VARCHAR(MAX)
         , mktVal  DECIMAL(38, 10)
      )
 INSERT INTO @top5Names
 
 SELECT TOP (@NumberOfResults)
      iss.IssuerName
    , fi.MktVal
 
 FROM [FSFundModel].[Reporting].Issuer iss
      
      INNER JOIN [FSFundModel].[Reporting].FundIssuer fi
      ON   iss.ExportId = fi.ExportId
      AND  fi.IssuerId  = iss.IssuerId
 
 WHERE iss.ExportId =
      (SELECT MAX(e.ExportId)
      FROM [FSFundModel].[Reporting].Export e
      WHERE e.FundId = @FundID
	  AND e.NAVDate = @NAVDate)
 ORDER BY MktVal DESC
 
 SELECT iss.IssuerName
    , id.InstrClass
    , iss.SubSector
    , fi.MktVal
    ,(fi.MktVal / f.MktVal) AS PctFund
 
 
 FROM [FSFundModel].[Reporting].Issuer iss
      
      
      INNER JOIN @top5Names tn
      ON   tn.IssName = iss.IssuerName
      INNER JOIN [FSFundModel].[Reporting].Instrument i
      ON   iss.ExportId = i.ExportId
      AND  iss.IssuerId = i.IssuerId
      
      
      INNER JOIN [FSFundModel].[Reporting].InstrumentDefinition id
      ON   id.ExportId = iss.ExportId
      AND  i.InstDefId = id.InstDefId
      
      INNER JOIN [FSFundModel].[Reporting].FundIssuer fi
      ON   iss.ExportId = fi.ExportId
      AND  fi.IssuerId  = iss.IssuerId
      
      
      INNER JOIN [FSFundModel].[Reporting].Fund f
      ON   iss.ExportId = f.ExportId
      
  
 WHERE iss.ExportId =
      (SELECT MAX(e.ExportId)
      FROM [FSFundModel].[Reporting].Export e
      WHERE e.FundId = @FundID
      AND  e.NAVDate = @NAVDate)
 
 AND  f.FundId = @FundID
 
 ORDER BY fi.MktVal DESC
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live/CharlieWaterman', 'SCHEMA', N'dbo', 'PROCEDURE', N'GetTopXIssuersByMV', NULL, NULL
GO
